package com.cpi.cornpp.purchase.program.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.purchase.program.dto.PurchaseOrderChargeDTO;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cpi.cornpp.transports.exception.TransportException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class PurchaseOrderChargeDAO extends DBDAO {
	private static final String SQL_INSERT_PURCHASE_ORDER_COST = "INSERT INTO purchases_orders_costs (cost_element_id, purchase_order_id, amount) VALUES (?, ?, ?)";
	private static final String SQL_UPDATE_PURCHASE_ORDER_COST_AMOUNT = "UPDATE purchases_orders_costs SET amount = ?";
	private static final String SQL_SELECT_PURCHASE_ORDER_COST = "SELECT purchases_orders_costs.cost_element_id, purchase_order_id, amount, short_name FROM purchases_orders_costs inner join costs_elements on purchases_orders_costs.cost_element_id = costs_elements.cost_element_id";
	private static final String SQL_DELETE_PURCHASE_ORDER_COST = "DELETE FROM purchases_orders_costs WHERE purchase_order_id = ? and cost_element_id = ?";
	private static final String SQL_EXISTS_PURCHASE_ORDER_COST = "select amount from purchases_orders_costs where purchase_order_id = ? and cost_element_id = ?";
	
	public boolean existsCharge(PurchaseOrderChargeDTO purchaseOrderChargeDTO)throws PurchaseProgramException{
		boolean exists = false;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			preparedStatement = prepareSQL(SQL_EXISTS_PURCHASE_ORDER_COST, dsName);
			preparedStatement.setInt(1, purchaseOrderChargeDTO.getPurchaseOrderId().intValue());
			preparedStatement.setInt(2, purchaseOrderChargeDTO.getChargeTypeId().intValue());
			resultSet = executeQuery(preparedStatement);
			exists = resultSet.next();
		} catch(PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(poolException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			poolException.printStackTrace(System.err);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(sqlException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			sqlException.printStackTrace(System.err);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqlException){
				sqlException.printStackTrace(System.out);
			}
		}
		return exists;
	}
	
	public void delete(PurchaseOrderChargeDTO purchaseOrderChargeDTO)throws PurchaseProgramException{
		PreparedStatement preparedStatement = null;
		try{
			preparedStatement = prepareSQL(SQL_DELETE_PURCHASE_ORDER_COST, dsName);
			preparedStatement.setInt(1, purchaseOrderChargeDTO.getPurchaseOrderId().intValue());
			preparedStatement.setInt(2, purchaseOrderChargeDTO.getChargeTypeId().intValue());
			executeUpdate(preparedStatement);
		} catch(PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(poolException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			poolException.printStackTrace(System.err);
			throw purchaseProgramException;
		} catch(SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			purchaseProgramException = new PurchaseProgramException(sqlException, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_DELETE);
			sqlException.printStackTrace(System.err);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			} catch(SQLException sqlException){
				sqlException.printStackTrace(System.out);
			}
		}
	}
	
	public int insertPurchaseOrderCharge(PurchaseOrderChargeDTO dto) throws PurchaseProgramException{
		int insertedRows = 0;
		PreparedStatement pstm = null;
		
		try{
			
			if (dto != null){
				pstm = prepareSQL(SQL_INSERT_PURCHASE_ORDER_COST, dsName);
				
				pstm.setInt(1, dto.getChargeTypeId().intValue());
				pstm.setInt(2, dto.getPurchaseOrderId().intValue());
				pstm.setDouble(3, dto.getAmount().doubleValue());
				
				insertedRows = executeInsert(pstm);
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_INSERT, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_INSERT);
				
				throw ppe;
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;			
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return insertedRows;
	}
	
	public int updatePurchaseOrderChargeAmountById(PurchaseOrderChargeDTO purchaseOrderChargeDTO) throws PurchaseProgramException{
		int updatedRows = 0;
		PreparedStatement pstm = null;
		StringBuffer sql = null;
		
		try{
			
			if (purchaseOrderChargeDTO != null){
				sql = new StringBuffer(SQL_UPDATE_PURCHASE_ORDER_COST_AMOUNT);
				
				sql.append(SQL_WHERE);
				sql.append(PurchaseOrderChargeDTO.PURCHASE_ORDER_ID);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				sql.append(SQL_AND);
				sql.append(PurchaseOrderChargeDTO.PURCHASE_cost_element_id);
				sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
				
				pstm = prepareSQL(sql.toString(), dsName);
				
				pstm.setObject(1, purchaseOrderChargeDTO.getAmount());
				pstm.setObject(2, purchaseOrderChargeDTO.getPurchaseOrderId());
				pstm.setObject(3, purchaseOrderChargeDTO.getChargeTypeId());
				
				updatedRows = executeUpdate(pstm);
			} else {
				PurchaseProgramException ppe = new PurchaseProgramException(MSG_NO_UPDATE, PurchaseProgramException.LAYER_DAO, PurchaseProgramException.ACTION_UPDATE);
				
				throw ppe;
			}
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_UPDATE);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;			
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return updatedRows;
	}
	
	public List getPurchaseOrderChargesByPurchaseOrder(int purchaseOrderId) throws PurchaseProgramException{
		List purchaseOrderCharges = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		StringBuffer sql = null;
		
		try{
			
			purchaseOrderCharges = new ArrayList(0);
			sql = new StringBuffer(SQL_SELECT_PURCHASE_ORDER_COST);
			
			sql.append(SQL_WHERE);
			sql.append(PurchaseOrderChargeDTO.PURCHASE_ORDER_ID);
			sql.append(PREPARED_STATEMENT_PARAMETER_TOKEN_ASSIGNMENT);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			pstm.setInt(1, purchaseOrderId);
			
			rs = executeQuery(pstm);
			purchaseOrderCharges = mapRows(rs);
			
		} catch(PoolException poe){
			PurchaseProgramException ppe = new PurchaseProgramException(poe, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			
			poe.printStackTrace(System.out);
			
			throw ppe;
		} catch(SQLException sqle){
			PurchaseProgramException ppe = new PurchaseProgramException(sqle, TransportException.LAYER_DAO, TransportException.ACTION_INSERT);
			
			sqle.printStackTrace(System.out);
			
			throw ppe;
		} finally {
			
			try{
				
				closeConnection(pstm.getConnection());
				
			} catch(SQLException sqle){
				sqle.printStackTrace(System.out);
			}
		}
		
		return purchaseOrderCharges;
	}
	
	private PurchaseOrderChargeDTO mapRow(ResultSet rs, int index) throws SQLException{
		PurchaseOrderChargeDTO purchaseOrderChargeDTO = null;
		
		if ((rs != null) && (rs.absolute(index))){
			purchaseOrderChargeDTO = new PurchaseOrderChargeDTO();
			
			purchaseOrderChargeDTO.setChargeTypeId(new Integer(rs.getInt(PurchaseOrderChargeDTO.PURCHASE_cost_element_id)));
			purchaseOrderChargeDTO.setPurchaseOrderId(new Integer(rs.getInt(PurchaseOrderChargeDTO.PURCHASE_ORDER_ID)));
			purchaseOrderChargeDTO.setAmount(new Double(rs.getDouble(PurchaseOrderChargeDTO.PURCHASE_ORDER_CHARGE_AMOUNT)));
			purchaseOrderChargeDTO.setChargeTypeCode(rs.getString(PurchaseOrderChargeDTO.PURCHASE_cost_element_code));
		}
		
		return purchaseOrderChargeDTO;
	}
	
	private List mapRows(ResultSet rs) throws SQLException{
		List purchaseOrderCharges = null;
		
		purchaseOrderCharges = new ArrayList(0);
		
		if (rs != null){
			
			for (; rs.next(); ){
				purchaseOrderCharges.add(mapRow(rs, rs.getRow()));
			}
		}
		
		return purchaseOrderCharges;
	}
}